﻿// ==================================================================================================
// Copyright(C) 2018 FI Ltd. All rights reserved.
//
// SYSTEM NAME	   : FI.Net Application Framework 
// COMPONENT ID	   : FI.Helpers.Data.DataAccessConfigurationView
// COMPONENT DESC  : Represents a view to navigate the configuration data.	
//
// CREATED DATE/BY : 18 Aug 2018 / Tian Mi
//
// REVISION HISTORY:	DATE/BY			SR#/CS/PM#/OTHERS		DESCRIPTION OF CHANGE
// ==================================================================================================

using System;
using System.Data;
using System.Data.Common;
using FI.Core.Data;


namespace FI.Helpers.Data
{
    /// <summary>
    /// Define a series of extension method against the <see cref="Database"/>.
    /// </summary>
    public static class DatabaseExtension
    {

        //UpdateBatchSize = 1, disables batch updates 
        //UpdateBatchSize = X where X > 1, sends x statements to the database at a time 
        //UpdateBatchSize = 0, sends the maximum number of statements at a time allowed by the server 
        //UpdatedRowSource can only be set to either UpdatedRowSource.None or UpdatedRowSource.OutputParameters.
        private static void DoAssignDataAdapterCommand(Database database, DbDataAdapter adapter, DbCommand selectCommand, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, DbTransaction transaction, int batchSize)
        {
            bool isBatchUpdate;

            isBatchUpdate = (batchSize > 1 || batchSize == 0);

            IDbDataAdapter adapter2 = adapter;
            adapter2.SelectCommand = selectCommand;
            if (insertCommand != null)
            {
                if (isBatchUpdate)
                {
                    insertCommand.UpdatedRowSource = (HasOutPutParameter(insertCommand, database)) ? UpdateRowSource.OutputParameters : UpdateRowSource.None;
                }

                adapter2.InsertCommand = insertCommand;

                if (null != transaction)
                {
                    insertCommand.Transaction = transaction;
                    insertCommand.Connection = transaction.Connection;
                }

            }
            if (updateCommand != null)
            {
                if (isBatchUpdate)
                {
                    updateCommand.UpdatedRowSource = (HasOutPutParameter(updateCommand, database)) ? UpdateRowSource.OutputParameters : UpdateRowSource.None;
                }

                adapter2.UpdateCommand = updateCommand;
                if (null != transaction)
                {
                    updateCommand.Transaction = transaction;
                    updateCommand.Connection = transaction.Connection;
                }
            }
            if (deleteCommand != null)
            {
                if (isBatchUpdate)
                {
                    deleteCommand.UpdatedRowSource = (HasOutPutParameter(deleteCommand, database)) ? UpdateRowSource.OutputParameters : UpdateRowSource.None;
                }

                adapter2.DeleteCommand = deleteCommand;
                if (null != transaction)
                {
                    deleteCommand.Transaction = transaction;
                    deleteCommand.Connection = transaction.Connection;
                }
            }

            if (isBatchUpdate)
            {
                adapter.UpdateBatchSize = batchSize;
            }
        }

        private static bool HasOutPutParameter(DbCommand command, Database database)
        {
            foreach (IDataParameter parameter in command.Parameters)
            {
                if (parameter.Direction == ParameterDirection.Output ||
                    parameter.Direction == ParameterDirection.InputOutput)
                    return true;
            }

            return false;
        }

        /// <summary>
        /// Updates the data set.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="dataRow">The data row.</param>
        /// <param name="insertCommand">The insert command.</param>
        /// <param name="updateCommand">The update command.</param>
        /// <param name="deleteCommand">The delete command.</param>
        /// <param name="updateBehavior">The update behavior.</param>
        /// <param name="acceptChangesDuringUpdate">if set to <c>true</c> [accept changes during update].</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The count of affected rows.</returns>
        public static int UpdateDataSet(this Database database, DataRow[] dataRow, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, UpdateBehavior updateBehavior, bool acceptChangesDuringUpdate, int updateBatchSize = 1)
        {
            int rowAffected = 0;

            using (DbConnection connection = database.CreateConnection())
            {
                connection.Open();
                if (null != insertCommand)
                {
                    insertCommand.Connection = connection;
                }

                if (null != updateCommand)
                {
                    updateCommand.Connection = connection;
                }
                if (null != deleteCommand)
                {
                    deleteCommand.Connection = connection;
                }

                if (updateBehavior == UpdateBehavior.Transactional)
                {
                    var transaction = connection.BeginTransaction();
                    using (var dbAdapter = database.DbProviderFactory.CreateDataAdapter())
                    {
                        dbAdapter.AcceptChangesDuringUpdate = acceptChangesDuringUpdate;
                        try
                        {
                            DoAssignDataAdapterCommand(database, dbAdapter, null, insertCommand, updateCommand, deleteCommand, transaction, updateBatchSize);

                            rowAffected = dbAdapter.Update(dataRow);
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                else
                {
                    using (var dbAdapter = database.DbProviderFactory.CreateDataAdapter())
                    {
                        dbAdapter.AcceptChangesDuringUpdate = acceptChangesDuringUpdate;
                        DoAssignDataAdapterCommand(database, dbAdapter, null, insertCommand, updateCommand, deleteCommand, null, updateBatchSize);

                        rowAffected = dbAdapter.Update(dataRow);
                    }
                }
            }
            return rowAffected;
        }

        /// <summary>
        /// Updates the data set.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="dataRow">The data row.</param>
        /// <param name="insertCommand">The insert command.</param>
        /// <param name="updateCommand">The update command.</param>
        /// <param name="deleteCommand">The delete command.</param>
        /// <param name="transaction">The transaction.</param>
        /// <param name="acceptChangesDuringUpdate">if set to <c>true</c> [accept changes during update].</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The affected row count.</returns>
        public static int UpdateDataSet(this Database database, DataRow[] dataRow, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, DbTransaction transaction, bool acceptChangesDuringUpdate, int updateBatchSize = 1)
        {
            using (var dbAdapter = database.DbProviderFactory.CreateDataAdapter())
            {
                dbAdapter.AcceptChangesDuringUpdate = acceptChangesDuringUpdate;
                DoAssignDataAdapterCommand(database, dbAdapter, null, insertCommand, updateCommand, deleteCommand, transaction, updateBatchSize);

                return dbAdapter.Update(dataRow);
            }
        }


        /// <summary>
        /// Assigns the parameter values.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="command">The command.</param>
        /// <param name="parameters">The parameters.</param>
        public static void AssignParameterValues(this Database database, DbCommand command, params object[] parameters)
        {
            if (parameters == null)
            {
                return;
            }

            int j = 0;
            for (int i = 0; i < command.Parameters.Count; i++)
            {
                var parameter = command.Parameters[i];
                switch (parameter.Direction)
                {
                    case ParameterDirection.Input:
                        {
                            parameter.Value = parameters[j++];
                            break;
                        }
                    case ParameterDirection.ReturnValue:
                        {
                            break;
                        }
                    case ParameterDirection.Output:
                        {
                            parameter.Value = null;
                            break;
                        }
                    case ParameterDirection.InputOutput:
                        {
                            parameter.Value = DBNull.Value;
                            break;
                        }
                }
            }
        }
    }
}
